/**
 * 
 */
package mdb.com.vn.persist;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

import org.hibernate.Hibernate;
import org.springframework.stereotype.Repository;

import mdb.com.vn.model.LoansSMS;

/**
 * @author tu.dod
 * 
 */
@Repository("loansSmsDAO")
public class LoansSmsDAOImpl extends AbstractHibernateDAOImpl<LoansSMS, String>
		implements LoansSmsDAO, Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	@Override
	protected Class<LoansSMS> getDomainClass() {
		// TODO Auto-generated method stub
		return LoansSMS.class;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<LoansSMS> findByAttribute(Date processDate, String status) {
		// TODO Auto-generated method stub
		return getSession().getNamedQuery("LoansSMS.findByStatusDate")
				.setDate("processDate", processDate)
				.setString("status", status).list();
	}

	@Override
	public LoansSMS findByLD(String LD) {
		// TODO Auto-generated method stub
		return (LoansSMS) getSession().getNamedQuery("LoansSMS.findByLD")
				.setString("refNo", LD).uniqueResult();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<LoansSMS> findByAttribute(String status) {
		// TODO Auto-generated method stub
		return getSession().getNamedQuery("LoansSMS.findByStatus")
				.setString("status", status).list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object> findByPropertiesToExcell(String status, Date processDate) {
		String sql = "SELECT DISTINCT (L.REF_NO) AS LD,"
				+ "L.BRANCH_NAME AS BRANCH,"
				+ "L.CREATED_DATE AS CREATED_DATE,"
				+ "L.CUSTOMER_NAME AS CUSTOMER," + "L.CUSTOMER_ID AS GCIF,"
				+ "L.PHONE_NUMBER AS PHONE," + "L.PR_AMOUNT AS AMOUNT,"
				+ "L.INTEREST_RATE AS RATE," + "L.PRODUCT_CODE AS PRODUCT,"
				+ "L.TERM AS TERM," + "L.DEALER AS DEALER,"
				+ "L.CUSTOMER_TYPE AS CUSTOMER_TYPE," + "L.LKD_NUM AS NUMLKD"
				+ ",GET_LUCKY_DRAW_NUM(L.REF_NO) AS LDK_DETAILS "
				+ "FROM IFTB_LOANS_SMS L "
				+ "WHERE L.STATUS = :STATUS AND L.PROCESS_DATE = :PROCESS_DATE";
		return getSession().createSQLQuery(sql)
				.addScalar("LD", Hibernate.STRING)
				.addScalar("BRANCH", Hibernate.STRING)
				.addScalar("CREATED_DATE", Hibernate.DATE)
				.addScalar("CUSTOMER", Hibernate.STRING)
				.addScalar("GCIF", Hibernate.STRING)
				.addScalar("PHONE", Hibernate.STRING)
				.addScalar("AMOUNT", Hibernate.DOUBLE)
				.addScalar("RATE", Hibernate.DOUBLE)
				.addScalar("PRODUCT", Hibernate.STRING)
				.addScalar("TERM", Hibernate.STRING)
				.addScalar("DEALER", Hibernate.STRING)
				.addScalar("CUSTOMER_TYPE", Hibernate.STRING)
				.addScalar("NUMLKD", Hibernate.STRING)
				.addScalar("LDK_DETAILS", Hibernate.STRING)
				.setString("STATUS", status)
				.setDate("PROCESS_DATE", processDate).list();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object> findByPropertiesToExcell(String status) {
		String sql = "SELECT DISTINCT (L.REF_NO) AS LD,"
				+ "L.BRANCH_NAME AS BRANCH,"
				+ "L.CREATED_DATE AS CREATED_DATE,"
				+ "L.CUSTOMER_NAME AS CUSTOMER," + "L.CUSTOMER_ID AS GCIF,"
				+ "L.PHONE_NUMBER AS PHONE," + "L.PR_AMOUNT AS AMOUNT,"
				+ "L.INTEREST_RATE AS RATE," + "L.PRODUCT_CODE AS PRODUCT,"
				+ "L.TERM AS TERM," + "L.DEALER AS DEALER,"
				+ "L.CUSTOMER_TYPE AS CUSTOMER_TYPE," + "L.LKD_NUM AS NUMLKD"
				+ ",GET_LUCKY_DRAW_NUM(L.REF_NO) AS LDK_DETAILS "
				+ "FROM IFTB_LOANS_SMS L "
				+ "WHERE L.STATUS = :STATUS";
		return getSession().createSQLQuery(sql)
				.addScalar("LD", Hibernate.STRING)
				.addScalar("BRANCH", Hibernate.STRING)
				.addScalar("CREATED_DATE", Hibernate.DATE)
				.addScalar("CUSTOMER", Hibernate.STRING)
				.addScalar("GCIF", Hibernate.STRING)
				.addScalar("PHONE", Hibernate.STRING)
				.addScalar("AMOUNT", Hibernate.DOUBLE)
				.addScalar("RATE", Hibernate.DOUBLE)
				.addScalar("PRODUCT", Hibernate.STRING)
				.addScalar("TERM", Hibernate.STRING)
				.addScalar("DEALER", Hibernate.STRING)
				.addScalar("CUSTOMER_TYPE", Hibernate.STRING)
				.addScalar("NUMLKD", Hibernate.STRING)
				.addScalar("LDK_DETAILS", Hibernate.STRING)
				.setString("STATUS", status).list();
	}

}
